/**
 * 
 */
package fr.greta.centreFormation.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import fr.greta.centreFormation.bean.Cursus;
import fr.greta.centreFormation.bean.Domaine;



/**
 * @author Betty Boop
 *
 */
public class DomaineHome {


	public static ArrayList<Domaine> selectAllDomaine(){
		ArrayList<Domaine> tabDomaine = new ArrayList<Domaine>();
		String sql = "SELECT * FROM domaine";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();
			while (result.next()){
				int idDomaine= result.getInt(1);
				String intituleDomaine = result.getString(2);
				String descriptionDomaine = result.getString(3);
				String CodeDomaineProfessionnelDomaine = result.getString(4);


				Domaine unDomaine = new Domaine();
				unDomaine.setIdDomaine(idDomaine);
				unDomaine.setIntituleDomaine(intituleDomaine);
				unDomaine.setDescriptionDomaine(descriptionDomaine);
				unDomaine.setCodeDomaineProfessionnelDomaine(CodeDomaineProfessionnelDomaine);

				tabDomaine.add(unDomaine);
			}
			return tabDomaine;

		} catch (Exception e) {
			e.getMessage();
			return null;
		}
	}

	public static int insertDomaine(Domaine unDomaine){

		int idDomaine = 0;
		String intituleDomaine = unDomaine.getIntituleDomaine();
		String descriptionDomaine = unDomaine.getDescriptionDomaine();
		String codeDomaineProf = unDomaine.getCodeDomaineProfessionnelDomaine();

		String sql = "INSERT INTO domaine(intitule_domaine, description_domaine, " +
				"code_domaine_professionnel_domaine) VALUE (?,?,?)";
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, intituleDomaine);
			stmt.setString(2, descriptionDomaine);
			stmt.setString(3, codeDomaineProf);

			stmt.executeUpdate();

			ResultSet result = stmt.getGeneratedKeys();
			System.out.println("apres le getgeneratedkeys");
			result.next();
			idDomaine = result.getInt(1);

			return idDomaine;

		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}


	}

	public static boolean deleteDomaine(Domaine unDomaine){
		int idDomaineRecherche = unDomaine.getIdDomaine();
		String sql = "DELETE FROM domaine WHERE id_domaine="+idDomaineRecherche;


		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.executeUpdate();
			return true;

		} catch (Exception e) {
			e.getStackTrace();
			return false;
		}
	}

	public static boolean updateDomaine(Domaine unDomaine){
		int idDomaine = unDomaine.getIdDomaine();
		String intituleDomaine = unDomaine.getIntituleDomaine();
		String descriptionDomaine = unDomaine.getDescriptionDomaine();
		String codeDomaine = unDomaine.getCodeDomaineProfessionnelDomaine();

		String sql = "UPDATE domaine SET intitule_domaine=?, description_domaine=?, code_domaine_professionnel_domaine=? WHERE id_domaine = ?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setString(1, intituleDomaine);
			stmt.setString(2, descriptionDomaine);
			stmt.setString(3, codeDomaine);
			stmt.setInt(4, idDomaine);

			stmt.executeUpdate();
			return true;

		} catch (Exception e) {
			e.getStackTrace();
			return false;}
	}

	public static Domaine selectUnDomaineById(int idDomaine){
		String sql = "SELECT * FROM domaine WHERE id_domaine ="+idDomaine;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();
			result.next();
			String intituleDomaine = result.getString(2);
			String descriptionDomaine = result.getString(3);
			String codeDomaine = result.getString(4);

			Domaine domaine = new Domaine();
			domaine.setIdDomaine(idDomaine);
			domaine.setIntituleDomaine(intituleDomaine);
			domaine.setDescriptionDomaine(descriptionDomaine);
			domaine.setCodeDomaineProfessionnelDomaine(codeDomaine);

			return domaine;

		} catch (Exception e) {
			e.getStackTrace();
			return null;
		}

	}
	public static ArrayList<Cursus> selectAllCursusByDomaine(Domaine unDomaine){
		int idDomaine = unDomaine.getIdDomaine();
		ArrayList<Cursus> allCursus = new ArrayList<Cursus>();
		String sql ="SELECT * FROM cursus WHERE id_domaine="+idDomaine;

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);

			ResultSet result = stmt.executeQuery();

			while (result.next()){

				Cursus unCursus = new Cursus();
				unCursus.setIdCursus(result.getInt(1));
				unCursus.setIntituleCursus(result.getString(2));
				unCursus.setDescriptionCursus(result.getString(3));
				unCursus.setCodeRomeCursus(result.getString(4));

				allCursus.add(unCursus);
			}
			return allCursus;
		} catch (Exception e) {
			return null;
		}
	}
	
	public static Domaine selectUnDomaineByIdCursus(int idCursus){
		
		String sql ="SELECT * FROM domaine d INNER JOIN cursus c ON c.id_domaine = d.id_domaine WHERE c.id_cursus = ?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setInt(1, idCursus);
			ResultSet result = stmt.executeQuery();
			
			result.next();
			
			int idDomaine = result.getInt(1);
			String intituleDomaine = result.getString(2);
			String descriptionDomaine = result.getString(3);
			String codeDomaine = result.getString(4);

			Domaine domaine = new Domaine();
			domaine.setIdDomaine(idDomaine);
			domaine.setIntituleDomaine(intituleDomaine);
			domaine.setDescriptionDomaine(descriptionDomaine);
			domaine.setCodeDomaineProfessionnelDomaine(codeDomaine);

			return domaine;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}


}

